package com.balloon.io;

import com.balloon.logic.Property;

import java.sql.*;

/**
 * This class is used for handling connection between property and database
 */
public class PropertyDao {

    static Connection currentCon = null;
    static ResultSet rs = null;
    static Statement stmt = null;

    /**
     * This method is used to sotre property information into the database
     * @param property the property users want to store
     */
    public static void setPropertyInformation(Property property) {
        // Preparing some objects for connection
        try {

            //connect to DB
            currentCon = ConnectionManager.getConnection();
            stmt = currentCon.createStatement();

            int cityID = 0;
            int addressID = 0;

            //Query for cityID
            String selectQuery = "SELECT cityID from city where cityName='"
                    + property.getCity()
                    + "'";
            rs = stmt.executeQuery(selectQuery);
            if (rs.next()) {
                cityID = Integer.parseInt(rs.getString("cityID"));
            }

            //Insert data into address
            String insertString =
                    "INSERT INTO address (longitude,latitude,cityID,street,zipcode,description) VALUES ("
                    + property.getLocationX() + ","
                    + property.getLocationY() + ","
                    + cityID + ",' ',' ',' ');";
            System.out.println(insertString);
            stmt.executeUpdate(insertString);

            //Query for addressID
            selectQuery = "SELECT addressID from address where cityID="
                    + cityID
                    + ";";
            rs = stmt.executeQuery(selectQuery);
            if (rs.next()) {
                addressID = Integer.parseInt(rs.getString("addressID"));
            }

            //Insert data into properties
            insertString = "INSERT INTO properties (propertyName,email,website,phone,openTime,description,addressID,categoryID) VALUES ('"
                    + property.getName() + "','"
                    + property.getPropertyEmail() + "','"
                    + property.getWebsite() + "','"
                    + property.getPhone() + "',' ','"
                    + property.getDescription() + "',"
                    + addressID + ","
                    + property.getPropertyType() + ");";

            System.out.println(insertString);
        } catch (Exception ex) {
            System.out.println("Log In failed: An Exception has occurred! " + ex);
        } //Some exception handling
        finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {
                }
                rs = null;
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception e) {
                }
                stmt = null;
            }

            if (currentCon != null) {

                try {
                    currentCon.close();
                } catch (Exception e) {
                }
                currentCon = null;

            }
        }

    }
}
